NoticeExecReport

V8 Message Definiton

METADATA

Attribute	Value
Topic	2450-liquidity-notice
MLink Token	SRATS
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
noticeNumber	CHAR(19)	PRI	'0000-0000-0000-0000'	AuctionNoticenoticeNumber
baseParentNumber	CHAR(19)	PRI	'0000-0000-0000-0000'
ticker_at	enum - AssetType		'None'	underlier ticker
ticker_ts	enum - TickerSrc		'None'	underlier ticker
ticker_tk	VARCHAR(12)		''	underlier ticker
tradeDate	DATE		'1900-01-01'
accnt	VARCHAR(16)		''	SR Accnt
clientFirm	VARCHAR(16)		''	SR ClientFirm
noticeDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	either notice create datetime or notice arrivalSR datetime high precision
responseId	VARCHAR(24)		''	from NoticeResponseresponseId most recently processed response to noticeNumber for this accntclientFirm pair
respOrigin	enum - SpdrSource		'None'
stageType	enum - SpdrStageType		'None'
auctionType	enum - AuctionType		'None'
auctionSource	enum - AuctionSource		'None'	source of the auction notice eg SRC MIAX etc
respDttm	DATETIME(6)		'1900-01-01 00:00:00.000000'	response arrival dttm high precision
respSide	enum - BuySell		'None'
respSize	INT		0
respActiveSize	INT		0	active response order size zero for ActiveHold
respPrice	DOUBLE		0	effRespPrice respPrice de uMid refUPrc 05 ga uMid refUPrc2
refUPrc	DOUBLE		0	reference price for tied to underlier orders midmarket tie block auctions only
refDe	FLOAT		0	reference delta for tied to underlier orders zero if tie is not desired block auctions only
refGa	FLOAT		0	reference gamma for tied to underlier orders zero if tie is not desired block auctions only
roundRule	enum - RoundRule		'None'	Exact 100 or Fuzzy 90 default is Exact block auctions only
stepUpIncr	DOUBLE		0	additional price increment best price typically only used if auction is competitive
riskGroupId	CHAR(19)		'0000-0000-0000-0000'	any auction response is associated with this riskGroupID and SpdrRiskGroupControl
strategy	VARCHAR(32)		''	user strategy field visible on SR tools
userData1	TINYTEXT		''	user data field free text from NoticeResponse
respStatus	enum - SpdrOrderStatus		'PendNew'
respDetail	TINYTEXT		''
lastUPrc	DOUBLE		0	effective uPrc midmarket from last trial pass
lastTrialPrc	DOUBLE		0	effective response price from last trial pass
leavesQty	INT		0	open order quantity
cumFillQty	INT		0	pkg cumFillQty if MLeg
avgFillPrice	DOUBLE		0	pkg avgFillPrice if MLeg
addLatency	DOUBLE		0	latency from notice arrivalSR to external response send in seconds OR latency from notice create to response accepted in seconds SRC Auctions
ackLatency	DOUBLE		0	latency from external response send to external response ack FRT in seconds external auctions only
responseResult	enum - NoticeResult		'None'
responseTime	SMALLINT		0	notice response arrival time milliseconds since auction start
prtPrice	DOUBLE		0	reported print price pkgPrice if MLeg
prtSize	INT		0	reported print size pgkSize if MLeg
prtTime	DATETIME(6)		'1900-01-01 00:00:00.000000'	reported print time 1st print if MLeg
prtUBid	DOUBLE		0	best estimate of uBid auction print time
prtUAsk	DOUBLE		0	best estimate of uAsk auction print time
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
OrderLegsList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
noticeNumber	1
baseParentNumber	2

JSON Block (OrderLegsList)

Field	Type	Comment
secKey	enum - secKey
secType	enum - SpdrKeyType
side	enum - BuySell
ratio	enum - ratio
positionType	enum - LegPositionType
legCumFillQty	enum - legCumFillQty
legAvgFillPrice	enum - legAvgFillPrice

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgNoticeExecReport` (
    `noticeNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'AuctionNotice.noticeNumber',
    `baseParentNumber` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'underlier ticker',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'underlier ticker',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01' COMMENT '',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR Accnt',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR ClientFirm',
    `noticeDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'either notice create datetime or notice arrival@SR datetime (high precision)',
    `responseId` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'from NoticeResponse.responseId (most recently processed response to noticeNumber for this accnt/clientFirm pair)',
    `respOrigin` ENUM('None','SpdrTicket','SpdrSingle','SRSE','FIX','HedgeTool','TradeHedge','OpenHedge','AutoHedge','Orphan','RiskManager','OrderManager','ManagedOrder','RFQRespSrvr','Legger','SRSEDrop','FixDrop','TicketDrop','SysTest','RFRResponse','AllocOmni','AllocClient','CertGateway','MLegResponse','LeggerX','DropManager','AutoHedgeSrvr','AuctionStrategySrvr','AllocBlockFace','AllocBlockCust','IceChatGateway','EXS2SRC','MLinkResponse','AutoResponderVD','AutoResponderRC','AutoResponderSN','AutoResponderBX','MLink') NOT NULL DEFAULT 'None',
    `stageType` ENUM('None','ModifyAny','ModifyAlgo') NOT NULL DEFAULT 'None',
    `auctionType` ENUM('None','Exposure','Improvement','Facilitation','Solicitation','Opening','Closing','RFQ','Block','Flash') NOT NULL DEFAULT 'None',
    `auctionSource` ENUM('None','SRC','AMEX','BOX','CBOE','ISE','NYSE','PHLX','NSDQ','BATS','C2','NQBX','MIAX','GMNI','EDGO','MCRY','MPRL','EMLD','MEMX','CME','CBOT','NYMEX','COMEX','ICE','EUREX') NOT NULL DEFAULT 'None' COMMENT 'source of the auction notice (eg. SRC, MIAX, etc.)',
    `respDttm` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'response arrival dttm (high precision)',
    `respSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None',
    `respSize` INT NOT NULL DEFAULT 0,
    `respActiveSize` INT NOT NULL DEFAULT 0 COMMENT 'active response order size (zero for ActiveHold)',
    `respPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effRespPrice = respPrice + de * (uMid - refUPrc) + 0.5 * ga * (uMid - refUPrc)^2',
    `refUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'reference price for tied to underlier orders (mid-market tie) (block auctions only)',
    `refDe` FLOAT NOT NULL DEFAULT 0 COMMENT 'reference delta for tied to underlier orders (zero if tie is not desired) (block auctions only)',
    `refGa` FLOAT NOT NULL DEFAULT 0 COMMENT 'reference gamma for tied to underlier orders (zero if tie is not desired) (block auctions only)',
    `roundRule` ENUM('None','Exact','Fuzzy') NOT NULL DEFAULT 'None' COMMENT 'Exact (100%) or Fuzzy (90%) [default is Exact] (block auctions only)',
    `stepUpIncr` DOUBLE NOT NULL DEFAULT 0 COMMENT 'additional price increment (best price) (typically only used if auction is competitive)',
    `riskGroupId` CHAR(19) NOT NULL DEFAULT '0000-0000-0000-0000' COMMENT 'any auction response is associated with this riskGroupID (and SpdrRiskGroupControl)',
    `strategy` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'user strategy field (visible on SR tools)',
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'user data field (free text) (from NoticeResponse)',
    `respStatus` ENUM('PendNew','New','PendClose','Closed','Rejected','SendReject') NOT NULL DEFAULT 'PendNew',
    `respDetail` TINYTEXT NOT NULL DEFAULT '',
    `lastUPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effective uPrc (mid-market) from last trial pass',
    `lastTrialPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effective response price from last trial pass',
    `leavesQty` INT NOT NULL DEFAULT 0 COMMENT 'open order quantity',
    `cumFillQty` INT NOT NULL DEFAULT 0 COMMENT 'pkg cumFillQty if MLeg',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'pkg avgFillPrice if MLeg',
    `addLatency` DOUBLE NOT NULL DEFAULT 0 COMMENT 'latency from notice arrival@SR to external response send (in seconds) OR latency from notice create to response accepted (in seconds) (SRC Auctions)',
    `ackLatency` DOUBLE NOT NULL DEFAULT 0 COMMENT 'latency from external response send to external response ack (FRT) (in seconds) (external auctions only)',
    `responseResult` ENUM('None','FullSize','AllocSize','PriceMiss','TooLate','OtherMiss','DidNotTrade','Pending') NOT NULL DEFAULT 'None',
    `responseTime` SMALLINT NOT NULL DEFAULT 0 COMMENT 'notice response arrival time (milliseconds since auction start)',
    `prtPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'reported print price (pkgPrice if MLeg)',
    `prtSize` INT NOT NULL DEFAULT 0 COMMENT 'reported print size (pgkSize if MLeg)',
    `prtTime` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'reported print time (1st print if MLeg)',
    `prtUBid` DOUBLE NOT NULL DEFAULT 0 COMMENT 'best estimate of uBid @ auction print time',
    `prtUAsk` DOUBLE NOT NULL DEFAULT 0 COMMENT 'best estimate of uAsk @ auction print time',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(OrderLegsList)),
    CONSTRAINT nonnegative_noticeNumber CHECK(ASCII(noticeNumber) < 56),
    CONSTRAINT nonnegative_baseParentNumber CHECK(ASCII(baseParentNumber) < 56),
    CONSTRAINT nonnegative_riskGroupId CHECK(ASCII(riskGroupId) < 56),
    PRIMARY KEY USING HASH (`noticeNumber`,`baseParentNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `noticeNumber`,
    `baseParentNumber`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `tradeDate`,
    `accnt`,
    `clientFirm`,
    `noticeDttm`,
    `responseId`,
    `respOrigin`,
    `stageType`,
    `auctionType`,
    `auctionSource`,
    `respDttm`,
    `respSide`,
    `respSize`,
    `respActiveSize`,
    `respPrice`,
    `refUPrc`,
    `refDe`,
    `refGa`,
    `roundRule`,
    `stepUpIncr`,
    `riskGroupId`,
    `strategy`,
    `userData1`,
    `respStatus`,
    `respDetail`,
    `lastUPrc`,
    `lastTrialPrc`,
    `leavesQty`,
    `cumFillQty`,
    `avgFillPrice`,
    `addLatency`,
    `ackLatency`,
    `responseResult`,
    `responseTime`,
    `prtPrice`,
    `prtSize`,
    `prtTime`,
    `prtUBid`,
    `prtUAsk`,
    `timestamp`,
    `OrderLegsList`
FROM `SRTrade`.`MsgNoticeExecReport`
WHERE 
    /* Replace with a CHAR(19) */
    `noticeNumber` = 'Example_noticeNumber'
  AND
    /* Replace with a CHAR(19) */
    `baseParentNumber` = 'Example_baseParentNumber';

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='NoticeExecReport' ORDER BY ordinal_position ASC;